﻿using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web.Http;

namespace Waiyun.WebApi
{
    public class WTSLD2Controller : ApiController
    {
        public static string ConnectionString;

        static WTSLD2Controller()
        {
            ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["waiyun_v2"].ConnectionString;
        }

        [HttpGet]
        public SingleResult<long> Total(string name)
        {
            if (string.Equals(name, "order", StringComparison.CurrentCultureIgnoreCase))
            {
                return TotalOrder();
            }
            else if (string.Equals(name, "cust", StringComparison.CurrentCultureIgnoreCase))
            {
                return TotalCustomer();
            }
            return new SingleResult<long>(0);
        }
        #region total
        private SingleResult<long> TotalOrder()
        {
            return ThreadPoolTool.RunSingleLong("wtsld2\\order", ConnectionString, (connection) =>
            {
                long value = 0;
                using (OracleCommand command = new OracleCommand(@"select count(1) from OD_F_STATS od where od.D_ORDER_TIME > sysdate - 30 and od.N_ACCEPT_NUM > 0", connection))
                {
                    using (OracleDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            value = Convert.ToInt64(reader[0]);
                        }
                        reader.Close();
                        return value;
                    }
                }
            });
        }

        public SingleResult<long> TotalCustomer()
        {
            string errorMessage = null;
            OracleConnection connection = ConnectionPool.GetConnection(ConnectionString, out errorMessage);
            if (connection == null)
            {
                return new SingleResult<long>(1, errorMessage);
            }
            try
            {
                long value = 0;
                using (OracleCommand command = new OracleCommand(@"select count(1)
 from (select distinct(V_STANDARD_CUST_ID) 
from PRD_DW.DF_ORDER_STATUS_HEAD h 
where h.d_create_time > sysdate-30 and h.V_STANDARD_CUST_ID in
('ESDY00101122','ESDY00100247','ESDH00001238','ESDY00101058','ESDY00100639','ETJF00001592','ESDY00101572','ETJF00001213','ETJF00001177'))", connection))
                {
                    using (OracleDataReader reader = command.ExecuteReader())
                    {

                        if (reader.Read())
                        {
                            value = Convert.ToInt64(reader[0]);
                        }
                        reader.Close();
                        return new SingleResult<long>(value);
                    }
                }
            }
            catch (Exception ex)
            {
                return new SingleResult<long>(1, ex.Message);
            }
            finally
            {
                ConnectionPool.Release(connection);
            }
        }
        #endregion

        [HttpGet]
        public SingleResult<Data<string, long>> XY(string name)
        {
            if (string.Equals(name, "order", StringComparison.CurrentCultureIgnoreCase))
            {
                return XYOrder();
            }
            else if (string.Equals(name, "cust", StringComparison.CurrentCultureIgnoreCase))
            {
                return XYCust();
            }
            return new SingleResult<Data<string, long>>(1, "unknown");
        }

        private SingleResult<Data<string, long>> XYOrder()
        {
            Data<string, long> cacheData = Cache.Get("WTSLD-XYORDER") as Data<string, long>;
            if (cacheData != null)
            {
                return new SingleResult<Data<string,long>>(cacheData);
            }

            OracleConnection connection = new OracleConnection(ConnectionString);
            try
            {
                connection.Open();

                using (OracleCommand command = new OracleCommand(@"select count(1),to_char(o.d_order_time,'yyyy-mm-dd') from OD_F_STATS o
where o.d_order_time>sysdate-30  group by to_char(o.d_order_time,'yyyy-mm-dd')
　　order by to_char(o.d_order_time,'yyyy-mm-dd')", connection))
                {
                    OracleDataAdapter adapter = new OracleDataAdapter(command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);

                    Data<string, long> data = new Data<string, long>();
                    for (int index = 0; index < dataTable.Rows.Count; index++)
                    {
                        data.XValues.Add(Convert.ToString(dataTable.Rows[index][1]));
                        data.YValues.Add(Convert.ToInt64(dataTable.Rows[index][0]));
                    }

                    Cache.Set("WTSLD-XYORDER", data);
                    return new SingleResult<Data<string, long>>(data);
                }
            }
            catch (Exception ex)
            {
                return new SingleResult<Data<string, long>>(1, ex.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        private SingleResult<Data<string, long>> XYCust()
        {
            Data<string, long> cacheData = Cache.Get("WTSLD-XYCUST") as Data<string, long>;
            if (cacheData != null)
            {
                return new SingleResult<Data<string, long>>(cacheData);
            }

            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                try
                {
                    connection.Open();

                    using (OracleCommand command = new OracleCommand(@"select (case h.V_STANDARD_CUST_ID 
when 'ESDH00001238' then '魏桥纺织'
when 'ESDY00100247' then '青岛前程环球海空通运'
when 'ESDY00100639' then '青岛和川国际物流'
when 'ESDY00101058' then '青岛瑞福达'
when 'ESDY00101122' then '青岛浩宏'
when 'ESDY00101572' then '山东澳源达'
when 'ETJF00001177' then '天津德迅'
when 'ETJF00001213' then '天津敦豪'
when 'ETJF00001592' then '天津海斯陆'
else h.V_STANDARD_CUST_ID end) as 客户,count(1) as SUM from  PRD_DW.DF_ORDER_STATUS_HEAD h where  h.d_create_time > sysdate-30 group by h.V_STANDARD_CUST_ID 
having h.V_STANDARD_CUST_ID in ('ESDY00101122','ESDY00100247','ESDH00001238','ESDY00101058','ESDY00100639','ETJF00001592','ESDY00101572','ETJF00001213','ETJF00001177')
order by SUM desc", connection))
                    {
                        using (OracleDataAdapter adapter = new OracleDataAdapter(command))
                        {
                            DataTable dataTable = new DataTable();
                            adapter.Fill(dataTable);

                            Data<string, long> data = new Data<string, long>();
                            for (int index = 0; index < dataTable.Rows.Count; index++)
                            {
                                data.XValues.Add(Convert.ToString(dataTable.Rows[index][0]));
                                data.YValues.Add(Convert.ToInt64(dataTable.Rows[index][1]));
                            }

                            Cache.Set("WTSLD-XYCUST", data);
                            return new SingleResult<Data<string, long>>(data);
                        }
                    }
                }
                catch (Exception ex)
                {
                    return new SingleResult<Data<string, long>>(1, ex.Message);
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}
